﻿Namespace SchoolClick.Database.DAL

    Public Interface IAlumnos
        ReadOnly Property IAlumnos As IAlumnos

        Function Buscar(texto As String) As IEnumerable(Of VIEW_LIST_ALUMNOS_ACTIVOS_WITH_LAST_GRADO)
        Function BuscarByNIE(NIE As String) As IEnumerable(Of VIEW_LIST_ALUMNOS_ACTIVOS_WITH_LAST_GRADO)
        Function Existe(IDAlumno As String) As Boolean
        Function GetInfo(IDAlumno As String) As V_INFO_ALUMNO
        Function GetResponsables(IDAlumno As String) As IEnumerable(Of V_INFO_RESPONSABLE_DEL_ALUMNO)
        Sub Actualizar(IDAlumno As String, DUI As String, Telefono As String, Celular As String, Correo As String, Direccion As String, MiembrosFam As Integer, Trabaja As Boolean, THijos As Boolean, ViveCon As String, Enfermedades As String, Medicamento As String, NIE As String, IsAlumno As Boolean)
        Sub RetirarloInstituto(IDAlumno As String)
        Function GetGradosInscritos(IDAlumno As String) As IEnumerable(Of V_TITULOS_GRADOS_ALUMNOS)
        Function NotasAlumnosrResumen(IDGrado As Integer, IDAlumno As String) As IEnumerable(Of NOTAS_ALUMNOS_ANIO_ESCOLAR)
        Function GetPeriodosEvaluativos(IDGrado As Integer, IDAlumno As String) As IEnumerable(Of PERIODOS_EVALUATIVOS_ALUMNO)
        Function GetMateriasCursaByGrado(IDGrado As Integer, IDAlumno As String) As IEnumerable(Of MATERIAS_CURSANDO_ALUMNO)
        Function GetNotasActividadesByPeriodo(IDGrado As Integer, IDAlumno As String, IDPeriodo As String, IDMateria As Integer) As IEnumerable(Of NOTAS_ACTIVIDADES_ALUMNO_BYPERIODO)
        Function GetNotasAlumnosWithMateriasSMByPeriodo(Año As String, IDPeriodo As String) As IEnumerable(Of UDF_NOTAS_ALUMNOS_MATERIA_SM)
        Sub SaveNotaAlumnoMateriaSMByPeriodo(IDAlumno As String, IDGrado As Integer, Nota As Decimal)
        Function GetAlumnosPeriodoRecuperacion(Año As String) As IEnumerable(Of ALUMNOS_PERIODO_RECUPERACION)
        Function GetDetailsAlumnosPeriodoRecuperacion(IDAlumno As String, Año As String) As IEnumerable(Of ALUMNOS_PERIODOS_RECUPERACION_DETAILS)
        Function GetNotasAlumnosPRByGrado(Año As String, IDGrado As String) As IEnumerable(Of NOTAS_ALUMNOS_PR_BYGRADO)
        Sub SaveNotaAlumnoPeriodoRecuperacion(ID As String, Nota As Decimal)
        Function GetAlumnosPeriodoRecuperacionXtra(Año As String) As IEnumerable(Of ALUMNOS_PERIODO_RECUPERACION_XTRA)
        Function GetNotasAlumnosPREByGrado(Año As String, IDGrado As String) As IEnumerable(Of NOTAS_ALUMNOS_PRE_BYGRADO)
        Sub SaveNotaAlumnoPeriodoRecuperacionXtra(ID As String, Nota As Decimal)
        Sub CambioCarrera(IDAlumno As String)
    End Interface

    Partial Public Class SchoolClickDAL
        Implements IAlumnos

        Public ReadOnly Property IAlumnos As IAlumnos Implements IAlumnos.IAlumnos
            Get
                Return DirectCast(Me, IAlumnos)
            End Get
        End Property

        Private Function Buscar(texto As String) As IEnumerable(Of VIEW_LIST_ALUMNOS_ACTIVOS_WITH_LAST_GRADO) Implements IAlumnos.Buscar
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.VIEW_LIST_ALUMNOS_ACTIVOS_WITH_LAST_GRADO.Where(Function(a) a.ALUMNO.Contains(texto)).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function BuscarByNIE(NIE As String) As IEnumerable(Of VIEW_LIST_ALUMNOS_ACTIVOS_WITH_LAST_GRADO) Implements IAlumnos.BuscarByNIE
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.VIEW_LIST_ALUMNOS_ACTIVOS_WITH_LAST_GRADO.Where(Function(a) a.NIE.Contains(NIE)).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function Existe(IDAlumno As String) As Boolean Implements IAlumnos.Existe
            Dim cnn As New SchoolClickEntities
            Try
                Return If(cnn.V_INFO_ALUMNO.Where(Function(a) a.ID_ALUMNO = IDAlumno).Count = 0, False, True)
            Catch ex As Exception
                ex.Data.Clear()
                Return False
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetInfo(IDAlumno As String) As V_INFO_ALUMNO Implements IAlumnos.GetInfo
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.V_INFO_ALUMNO.Where(Function(a) a.ID_ALUMNO = IDAlumno).Single
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetResponsables(IDAlumno As String) As IEnumerable(Of V_INFO_RESPONSABLE_DEL_ALUMNO) Implements IAlumnos.GetResponsables
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.V_INFO_RESPONSABLE_DEL_ALUMNO.Where(Function(r) r.ID_ALUMNO = IDAlumno).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub Actualizar(IDAlumno As String, DUI As String, Telefono As String, Celular As String, Correo As String, Direccion As String, MiembrosFam As Integer, Trabaja As Boolean, THijos As Boolean, ViveCon As String, Enfermedades As String, Medicamento As String, NIE As String, IsAlumno As Boolean) Implements IAlumnos.Actualizar
            Dim cnn As New SchoolClickEntities
            Try
                cnn.UPT_DATOS_ALUMNO(IDAlumno, DUI, Telefono, Celular, Correo, Direccion, MiembrosFam, Trabaja, THijos,
                                     ViveCon, Enfermedades, Medicamento, NIE, IsAlumno, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub RetirarloInstituto(IDAlumno As String) Implements IAlumnos.RetirarloInstituto
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_RETIRAR_ALUMNO_INSTITUTO(IDAlumno, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetGradosInscritos(IDAlumno As String) As IEnumerable(Of V_TITULOS_GRADOS_ALUMNOS) Implements IAlumnos.GetGradosInscritos
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.V_TITULOS_GRADOS_ALUMNOS.Where(Function(g) g.ID_ALUMNO = IDAlumno).ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function NotasAlumnosrResumen(IDGrado As Integer, IDAlumno As String) As IEnumerable(Of NOTAS_ALUMNOS_ANIO_ESCOLAR) Implements IAlumnos.NotasAlumnosrResumen
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.NOTA_ALUMNOS_GENERAL(IDGrado, IDAlumno).OrderBy(Function(n) n.MATRICULA).ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetPeriodosEvaluativos(IDGrado As Integer, IDAlumno As String) As IEnumerable(Of PERIODOS_EVALUATIVOS_ALUMNO) Implements IAlumnos.GetPeriodosEvaluativos
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.PERIODOS_EVA_ALUMNOS_BY_GRADO(IDAlumno, IDGrado).OrderBy(Function(n) n.ID_PERIODO).ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetMateriasCursaByGrado(IDGrado As Integer, IDAlumno As String) As IEnumerable(Of MATERIAS_CURSANDO_ALUMNO) Implements IAlumnos.GetMateriasCursaByGrado
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.MATERIAS_CURSANDO_ALUMNO_BY_GRADO(IDAlumno, IDGrado).ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetNotasActividadesByPeriodo(IDGrado As Integer, IDAlumno As String, IDPeriodo As String, IDMateria As Integer) As IEnumerable(Of NOTAS_ACTIVIDADES_ALUMNO_BYPERIODO) Implements IAlumnos.GetNotasActividadesByPeriodo
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.NOTAS_ACTIVIDADES_EVA_BYPERIODO(IDAlumno, IDGrado, IDPeriodo, IDMateria) _
                            .OrderBy(Function(n) n.ABREVIACION) _
                            .ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetNotasAlumnosWithMateriasSMByPeriodo(Año As String, IDPeriodo As String) As IEnumerable(Of UDF_NOTAS_ALUMNOS_MATERIA_SM) Implements IAlumnos.GetNotasAlumnosWithMateriasSMByPeriodo
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.UDF_NOTAS_ALUMNOS_MATERIA_SM_BYPERIODO(Año, IDPeriodo) _
                            .OrderByDescending(Function(g) g.ESPECIALIDAD) _
                            .ThenBy(Function(g) g.GRADE) _
                            .ThenBy(Function(g) g.SECCION) _
                            .ThenBy(Function(g) g.NOMBRE_COMPLETO) _
                            .ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub SaveNotaAlumnoMateriaSMByPeriodo(IDAlumno As String, IDGrado As Integer, Nota As Decimal) Implements IAlumnos.SaveNotaAlumnoMateriaSMByPeriodo
            Dim cnn As New SchoolClickEntities
            Try
                cnn.SAVE_NOTA_PERIODO_MATERIA_SM(IDAlumno, IDGrado, Nota,
                                                 ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetAlumnosPeriodoRecuperacion(Año As String) As IEnumerable(Of ALUMNOS_PERIODO_RECUPERACION) Implements IAlumnos.GetAlumnosPeriodoRecuperacion
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_ALUMNOS_PERIODO_RECUPERACION(Año).OrderByDescending(Function(a) a.ESPECIALIDAD) _
                                                                .ThenBy(Function(a) a.GRADO) _
                                                                .ThenBy(Function(a) a.SECCION) _
                                                                .ThenBy(Function(a) a.NOMBRE_COMPLETO) _
                                                                .ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetDetailsAlumnosPeriodoRecuperacion(IDAlumno As String, Año As String) As IEnumerable(Of ALUMNOS_PERIODOS_RECUPERACION_DETAILS) Implements IAlumnos.GetDetailsAlumnosPeriodoRecuperacion
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_ALUMNOS_PERIODOS_RECUPERACION_DETAILS(IDAlumno, Año).OrderBy(Function(a) a.MATRICULA) _
                                                                .ThenBy(Function(a) a.MATERIA) _
                                                                .ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetNotasAlumnosPRByGrado(Año As String, IDGrado As String) As IEnumerable(Of NOTAS_ALUMNOS_PR_BYGRADO) Implements IAlumnos.GetNotasAlumnosPRByGrado
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_NOTAS_ALUMNOS_PR_BYGRADO(Año, IDGrado).OrderBy(Function(a) a.SECCION) _
                                                                     .ThenBy(Function(a) a.NOMBRE_COMPLETO) _
                                                                     .ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub SaveNotaAlumnoPeriodoRecuperacion(ID As String, Nota As Decimal) Implements IAlumnos.SaveNotaAlumnoPeriodoRecuperacion
            Dim cnn As New SchoolClickEntities
            Try
                cnn.SAVE_NOTA_PERIODO_RECUPERACION(ID, Nota,
                                                 ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetAlumnosPeriodoRecuperacionXtra(Año As String) As IEnumerable(Of ALUMNOS_PERIODO_RECUPERACION_XTRA) Implements IAlumnos.GetAlumnosPeriodoRecuperacionXtra
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_ALUMNOS_PERIODO_RECUPERACION_XTRA(Año).OrderByDescending(Function(a) a.ESPECIALIDAD) _
                                                                     .ThenBy(Function(a) a.GRADO) _
                                                                     .ThenBy(Function(a) a.SECCION) _
                                                                     .ThenBy(Function(a) a.NOMBRE_COMPLETO) _
                                                                     .ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetNotasAlumnosPREByGrado(Año As String, IDGrado As String) As IEnumerable(Of NOTAS_ALUMNOS_PRE_BYGRADO) Implements IAlumnos.GetNotasAlumnosPREByGrado
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_NOTAS_ALUMNOS_PRE_BYGRADO(Año, IDGrado).OrderBy(Function(a) a.SECCION) _
                                                                     .ThenBy(Function(a) a.NOMBRE_COMPLETO) _
                                                                     .ToArray()
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub SaveNotaAlumnoPeriodoRecuperacionXtra(ID As String, Nota As Decimal) Implements IAlumnos.SaveNotaAlumnoPeriodoRecuperacionXtra
            Dim cnn As New SchoolClickEntities
            Try
                cnn.SAVE_NOTA_PERIODO_RECUPERACION_EXTRA(ID, Nota,
                                                 ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub CambioCarrera(IDAlumno As String) Implements IAlumnos.CambioCarrera
            Dim cnn As New SchoolClickEntities
            Try
                cnn.OP_CAMBIO_CARRERA_A_GENERAL(IDAlumno,
                                                 ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub
    End Class
End Namespace
